package cn.edu.cqvie.ssm.model.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.edu.cqvie.ssm.entity.Student;
import cn.edu.cqvie.ssm.model.StudentModel;
import cn.edu.cqvie.ssm.util.Dbconn;

/**
 * 学生信息管理实现
 * 
 * @author zhengsh
 *
 */
public class StudentModelImpl implements StudentModel {

	Dbconn db = Dbconn.builder().build();

	@Override
	public void insert(Student stu) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = db.getConnection();
			String sql = "insert into student values(?,?,?,?,?,?,?)";
			ps = con.prepareStatement(sql);

			ps.setInt(1, stu.getId());
			ps.setString(2, stu.getName());
			ps.setString(3, stu.getSex());
			ps.setInt(4, stu.getAge());
			ps.setString(5, stu.getGrade());
			ps.setInt(6, stu.getScore());
			ps.setString(7, stu.getPhoto());

			ps.executeUpdate();
		} catch (Throwable e) {
			e.printStackTrace();
		} finally {
			db.closeAll(con, ps, null);
		}
	}

	@Override
	public void update(Student stu) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = db.getConnection();
			StringBuilder sb = new StringBuilder(128);
			sb.append("update student set ");
			sb.append("name=?,");
			sb.append("sex=?,");
			sb.append("age=?,");
			sb.append("grade=?,");
			sb.append("score=?,");
			sb.append("photo=?");
			sb.append(" where id=?");
			
			ps = con.prepareStatement(sb.toString());

			ps.setString(1, stu.getName());
			ps.setString(2, stu.getSex());
			ps.setInt(3, stu.getAge());
			ps.setString(4, stu.getGrade());
			ps.setInt(5, stu.getScore());
			ps.setString(6, stu.getPhoto());
			ps.setInt(7, stu.getId());
			
			
			ps.executeUpdate();
		} catch (Throwable e) {
			e.printStackTrace();
		} finally {
			db.closeAll(con, ps, null);
		}
	}

	@Override
	public void delete(Student stu) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = db.getConnection();
			
			String sql = "delete from student where id=?" ;
			
			ps = con.prepareStatement(sql);
			ps.setInt(1, stu.getId());
			
			ps.executeUpdate();
		} catch (Throwable e) {
			e.printStackTrace();
		} finally {
			db.closeAll(con, ps, null);
		}
	}

	@Override
	public List<Student> list(Student stu) {
		return list(stu, 0, 0);
	}

	/**
	 * 查询学生列表
	 */
	public List<Student> list(Student stu, int startRow, int size) {
		List<Student> list = new ArrayList<>();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Object> arr = new ArrayList<>();
		try {
			con = db.getConnection();
			String sql = "select * from `student` where 1=1";
			if (stu != null) {
				if (stu.getId() != 0) {
					sql += " and `id`=?";
					arr.add(stu.getId());
				}
			}
			if (size != 0) {
				sql += " limit ?,?";
				arr.add(startRow);
				arr.add(size);
			}
			ps = con.prepareStatement(sql);
			for (int i = 0; i < arr.size(); i++) {
				Object v = arr.get(i);
				if (v instanceof Integer) {
					ps.setInt(i + 1, ((int) v));
				}
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				Student u = Student.builder()
						.id(rs.getInt("id"))
						.name(rs.getString("name"))
						.sex(rs.getString("sex"))
						.age(rs.getInt("age"))
						.grade(rs.getString("grade"))
						.score(rs.getInt("score"))
						.photo(rs.getString("photo"))
						.build();
				list.add(u);
			}
		} catch (Throwable e) {
			e.printStackTrace();
		} finally {
			db.closeAll(con, ps, rs);
		}
		return list;
	}
	
	@Override
	public Student get(int id) {
		List<Student> list = list(Student.builder().id(id).build());
		if (list != null && list.size() > 0) {
			return list.get(0);
		} else {
			return null;
		}
	}

	@Override
	public int countAllStudents() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		try {
			con = db.getConnection();
			String sql = "select count(1) from `student` ";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Throwable e) {
			e.printStackTrace();
		} finally {
			db.closeAll(con, ps, rs);
		}
		return count;
	}

	@Override
	public List<Student> list(int startRow, int size) {
		return list(null, startRow, size);
	}

}
